package com.util.DBpool;

import com.util.PoolUtil;

import java.sql.*;
import java.util.ArrayList;

/**
 * Created by wlc on 2017/11/14.
 */

public class DBconnection {
    /*表信息*/
    private String sheet_name;
    /*命令信息*/
    private Connection conn = null;
    private PreparedStatement pst = null;
    private  Statement stmt = null;
    private  String selectsql = null;      //设置SQL命令
    private  ResultSet retsult = null;//获得数据库返回结果
    /**
     * 设置数据成员
     */
    public void setSheet_name(String SHEETNAME){
        this.sheet_name = SHEETNAME;
    }

    /**
     *用于从数据库中获取所有表名
     * @return ArrayList<String>
     */
    public ArrayList<String> getTableNames() {
        ArrayList<String> tableNames = new ArrayList<>();

        PoolConnection pcn = PoolUtil.pool.getPoolConnection();
//        if(!pcn.getBusy()) {
//            System.out.println("Succeeded connecting to the Database!");
//        } else {
//            System.out.println("Failed connecting to the Database!");
//        }
        try {
            //获取数据库的元数据
            DatabaseMetaData db = pcn.getMetaData();
            //从元数据中获取到所有的表名
            retsult = db.getTables(null, null, null, new String[] { "TABLE" });
            while(retsult.next()) {
                tableNames.add(retsult.getString(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                retsult.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            pcn.close();
        }
        return tableNames;
    }

    /**
     * 用于从数据库指定表中获取所有数据
     * @return ArrayList<ArrayList<String>>
     */
    public ArrayList<ArrayList<String>> getDataFromDB() {
        int paraCount = getTitleFromDB().size(); //读取字段数量
        selectsql = "select * from " + sheet_name;
        PoolConnection pcn = PoolUtil.pool.getPoolConnection();

        ArrayList<ArrayList<String>> dataArray = new ArrayList<>();   //读取表
        try {
            pst = pcn.getPst(selectsql);
            retsult = pst.executeQuery(selectsql); //执行语句，得到结果集，用于存储指定表
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            //循环一次读取一行数据
            while (retsult.next()) {
                ArrayList<String> tempArray = new ArrayList<>();
                for (int i = 0; i < paraCount; i++) {
                    tempArray.add(retsult.getString(i + 1));
                }
                dataArray.add(tempArray);
            }
            //关闭连接
            retsult.close();
            pst.close();
            pcn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dataArray;
    }

    /**
     * 用于从数据库指定表中获取指定字段数据
     * @return ArrayList<String>
     */
    public ArrayList<String> getDataFromFiled(int col) {
        selectsql = "select * from " + sheet_name;
        PoolConnection pcn = PoolUtil.pool.getPoolConnection();

        ArrayList<String> col_dataArray = new ArrayList<>();   //读取表
        try {
            pst = pcn.getPst(selectsql);
            retsult = pst.executeQuery(selectsql);//执行语句，得到结果集，用于存储指定表
            //循环一次读取一行数据
            while (retsult.next()) {
                col_dataArray.add(retsult.getString(col+1));
            }
            //关闭连接
            retsult.close();
            pst.close();
            pcn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return col_dataArray;
    }

    /**
     * 用于从数据库指定表中获取ID指定行中指定字段的值
     * @param id
     * @param col_name
     * @return String
     */
    public String getDataFromRow(String id, String col_name) {
        String value = new String();
        selectsql = "select " + col_name + " from " + sheet_name + " where id = " +  id;
        PoolConnection pcn = PoolUtil.pool.getPoolConnection();

        try {
            pst = pcn.getPst(selectsql);
            retsult = pst.executeQuery(selectsql);
            while(retsult.next()) {
                value = retsult.getString(col_name);
            }
            retsult.close();
            pst.close();
            pcn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return value;
    }

    /**
     *用于从数据库指定表中获取所有字段名称
     * @return ArrayList<String>
     */
    public ArrayList<String> getTitleFromDB() {
        ArrayList<String> sheet_title = new ArrayList<>();
        //与数据库的连接
        selectsql = "select * from " + sheet_name;
        PoolConnection pcn = PoolUtil.pool.getPoolConnection();

        try {
            pst = pcn.getPst(selectsql);
            ResultSetMetaData rsmd = pst.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                sheet_title.add(rsmd.getColumnName(i + 1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pst != null) {
                try {
                    pst.close();
                    pcn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return sheet_title;
    }

    /**
     * 用于将数据存到数据库指定表
     * @param data
     */
    public boolean saveDataToDB(ArrayList<String> data) {
        String value = "values(";
        for (int i = 0;i < data.size()-1;i++){
            value = value + "?,";
        }
        value = value + "?)";

        selectsql = "insert into "+ sheet_name + " " + value;

        PoolConnection pcn = PoolUtil.pool.getPoolConnection();

        try {
            pst = pcn.getPst(selectsql);
            for (int i = 0; i < data.size(); i++){
                pst.setString(i+1,data.get(i));
            }
            //执行更新
            pst.executeUpdate();
            //关闭连接
            pst.close();
            pcn.close();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * 用于从数据库指定表中获取数据条数
     * @return int
     */
    public int getDataNumFromDB() {
        selectsql = "select COUNT(*) from " + sheet_name;

        PoolConnection pcn = PoolUtil.pool.getPoolConnection();
//        if(!pcn.getBusy()) {
//            System.out.println("Succeeded connecting to the Database!");
//        } else {
//            System.out.println("Failed connecting to the Database!");
//        }

        int dataNum = 0;
        try {
            pst = pcn.getPst(selectsql);
            retsult = pst.executeQuery();//执行语句，得到结果集，用于存储指定表
            //获取数据条数
            retsult.next();
            dataNum = retsult.getInt(1);
            //关闭连接
            retsult.close();
            pcn.close();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dataNum;
    }

    /**
     * 用于从数据库指定表中根据指定字段的值修改数据库中指定表中的指定字段值
     * @param changeField
     * @param changeData
     * @param accordField
     * @param accordData
     */
    public void changeDataToDB(String changeField,String changeData,String accordField,String accordData){
        selectsql = "update "+sheet_name+" set "+changeField+"='"+changeData+"' where "+accordField+"='"+accordData+"'";//生成一条mysql语句

        PoolConnection pcn = PoolUtil.pool.getPoolConnection();
//        if(!pcn.getBusy()) {
//            System.out.println("Succeeded connecting to the Database!");
//        } else {
//            System.out.println("Failed connecting to the Database!");
//        }
        try{
            stmt=pcn.creatStatement();//创建一个Statement对象
            stmt.executeUpdate(selectsql);//执行SQL语句
            stmt.close();
            pcn.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * 用于从数据库指定表中删除指定字段值等于某值的数据
     * @param accordField
     * @param accordData
     */
    public void deleteDataInDB(String accordField,String accordData){
       selectsql = "delete from "+sheet_name+" where "+accordField+"='"+accordData+"'";  //生成一条mysql语句

        PoolConnection pcn = PoolUtil.pool.getPoolConnection();
        try{
            stmt=pcn.creatStatement();//创建一个Statement对象
            stmt.executeUpdate(selectsql);//执行SQL语句
            stmt.close();
            pcn.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * 用于从数据库指定表中获取指定ID的一行数据
     * @param id
     * @return
     */
    public ArrayList<String> getRowDataFromDB(String id){
        int paraCount = getTitleFromDB().size(); //读取字段数量
        selectsql = "select * from "+sheet_name+" where id = "+id;  //生成一条mysql语句
        PoolConnection pcn = PoolUtil.pool.getPoolConnection();

        ArrayList<String> dataArray = new ArrayList<>();
        try {
            pst = pcn.getPst(selectsql);
            retsult = pst.executeQuery(selectsql);
            while(retsult.next()) {
                for (int i = 0; i < paraCount; i++) {
                    dataArray.add(retsult.getString(i + 1));
                }
            }
            retsult.close();
            pst.close();
            pcn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dataArray;
    }


    /**
     *  查询多个id
     * @param ids
     * @return
     */
    public ArrayList<ArrayList<String>> getRowsDataFromDB(ArrayList<String> ids) {
        int paraCount = getTitleFromDB().size();
        String selected_Id = String.join(",", (String [])ids.toArray());


        selectsql = "select * from " + sheet_name + " where id in (" + selected_Id + ")";

        PoolConnection pcn = PoolUtil.pool.getPoolConnection();
        ArrayList<ArrayList<String>> dataArrays = new ArrayList<>();
        try {
            pst = pcn.getPst(selectsql);
            retsult = pst.executeQuery(selectsql);
            while (retsult.next()) {
                ArrayList<String> tempArray = new ArrayList<>();
                for (int i = 0; i < paraCount; i++) {
                    tempArray.add(retsult.getString(i + 1));
                }
                dataArrays.add(tempArray);
            }
            retsult.close();
            pst.close();
            pcn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dataArrays;
    }

    /**
     * 由keyword查询ID
     * @param keyword
     * @param table_name
     * @return
     */
    public ArrayList<String> getIdByKeyword(String keyword, String table_name, String col_name) {
        selectsql = "select id from " + table_name + " where " + col_name + " = " + keyword;
        PoolConnection pcn = PoolUtil.pool.getPoolConnection();

        ArrayList<String> searched_id = new ArrayList<>();
        try {
            pst = pcn.getPst(selectsql);
            retsult = pst.executeQuery(selectsql);
            while (retsult.next()) {
                searched_id.add(retsult.getString(1));
            }
            retsult.close();
            pst.close();
            pcn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return searched_id;
    }

    /**
     *  多表连接查询数据(根据关键词)
     * @param join_col_name
     * * @param join_table
     * @return
     */
    public ArrayList<ArrayList<String>>getFormDataJoinTable(String select_col_name,
                                                            String join_col_name,
                                                            String keyword,
                                                            ArrayList<String> join_table) {
        selectsql = "select * from " + sheet_name;

        for(String table :join_table) {
            selectsql += " left join " + table + " using(" + join_col_name + ")";      //循环较少不用StringBuffer
        }
        if (keyword.length() > 0)
//            selectsql += " where " + select_col_name + " like " + "'%" + keyword + "%'";   // like 导致索引失效 查询速度变慢
            selectsql += " where " + select_col_name + "='" + keyword + "'";

        PoolConnection pcn = PoolUtil.pool.getPoolConnection();
        ArrayList<ArrayList<String>> dataArrays = new ArrayList<>();

        try {
            pst = pcn.getPst(selectsql);
            retsult = pst.executeQuery(selectsql);
            int paraCount = retsult.getMetaData().getColumnCount();       //结果集列数

            System.out.println("查询列数=" + paraCount);
            while (retsult.next()) {
                ArrayList<String> tempArray = new ArrayList<>();

                for (int i = 0; i < paraCount; i++) {
                    tempArray.add(retsult.getString(i + 1));
                }
                dataArrays.add(tempArray);
            }
            retsult.close();
            pst.close();
            pcn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dataArrays;
    }

    /**
     * 多表连接查询全部数据
     * @param join_col_name
     * @param join_table
     * @return ArrayList<ArrayList < String>>
     */
    public ArrayList<ArrayList<String>>getFormDataJoinTable_2(
                                                            String join_col_name,
                                                            ArrayList<String> join_table) {
        selectsql = "select * from " + sheet_name;
        for(String table :join_table) {
            selectsql += " left join " + table + " using(" + join_col_name + ")";      //循环较少不用StringBuffer了
        }

        PoolConnection pcn = PoolUtil.pool.getPoolConnection();
        ArrayList<ArrayList<String>> dataArrays = new ArrayList<>();
        try {
            pst = pcn.getPst(selectsql);
            retsult = pst.executeQuery(selectsql);
            int paraCount = retsult.getMetaData().getColumnCount();       //结果集列术

            System.out.println("查询列数=" + paraCount);

            while (retsult.next()) {
                ArrayList<String> tempArray = new ArrayList<>();

                for (int i = 0; i < paraCount; i++) {
                    tempArray.add(retsult.getString(i + 1));
                }
                dataArrays.add(tempArray);
            }
            retsult.close();
            pst.close();
            pcn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dataArrays;
    }

    /**
     * 单表由keyword查询指定列
     *
     * @param keyword
     * @param table_name
     * @param col_name
     * @param col_target
     * @return ArrayList<String>
     */

    public ArrayList<String> getColByKeyword(String keyword, String table_name, String col_name, String col_target) {
        selectsql = "select " + col_target + " from " + table_name + " where " + col_name + " = " + keyword;
        PoolConnection pcn = PoolUtil.pool.getPoolConnection();

        ArrayList<String> searched_id = new ArrayList<>();
        try {
            pst = pcn.getPst(selectsql);
            retsult = pst.executeQuery(selectsql);
            while (retsult.next()) {
                searched_id.add(retsult.getString(1));
            }
            retsult.close();
            pst.close();
            pcn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return searched_id;
    }
}
